<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>MySQL Python tutorial</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Python, MySQL, language, database, MySQLdb">
<meta name="description" content="This is MySQL Python tutorial. It covers 
the basics of MySQL programming with Python.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

<script type="text/javascript">
  (function() {
    var po = document.createElement('script'); po.type = 'text/javascript'; po.async = true;
    po.src = 'https://apis.google.com/js/plusone.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s);
  })();
</script>

<script type="text/javascript">

  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'UA-5536206-1']);
  _gaq.push(['_trackPageview']);

  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();

</script>
</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>


<h1>MySQL Python tutorial</h1>


<p>
This is a Python programming tutorial for the MySQL database. It covers 
the basics of MySQL programming with Python. It uses the MySQLdb module. 
The examples were created and tested on Ubuntu Linux. 
</p>

<p>
There is a similar <a href="/db/postgresqlpythontutorial/">PostgreSQL Python tutorial</a>, 
<a href="/db/mysqlvb/">MySQL Visual Basic tutorial</a>, 
or <a href="/databases/mysqlphptutorial/">MySQL PHP tutorial</a> on ZetCode.
If you need to refresh your knowledge of the Python language, there is a full 
<a href="/lang/python/">Python tutorial</a>. You may also consider 
to look at the <a href="/databases/mysqltutorial/">MySQL tutorial</a>, too.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<h2>About MySQL database</h2>

<p>
MySQL is a leading open source database management system. It is a multi user, 
multithreaded database management system. MySQL is especially popular on the web. 
It is one of the parts of the very popular <b>LAMP</b>
platform. Linux, Apache, MySQL, PHP. Currently MySQL is owned by Oracle.
MySQL database is available on most important OS
platforms. It runs under BSD Unix, Linux, Windows or Mac.
Wikipedia and YouTube use MySQL. These sites manage millions of queries
each day. MySQL comes in two versions. MySQL server system and MySQL
embedded system.
</p>

<h2>Before we start</h2>

<p>
We need to install several packages to execute the examples in this tutorial.
</p>

<p>
If you don't already have MySQL installed, we must install it. 
</p>

<pre>
$ sudo apt-get install mysql-server
</pre>

<p>
This command installs the MySQL server and various other packages. 
While installing the package, we are prompted to enter
a password for the MySQL root account.
</p>

<pre>
$ apt-cache search MySQLdb
python-mysqldb - A Python interface to MySQL
python-mysqldb-dbg - A Python interface to MySQL (debug extension)
bibus - bibliographic database
eikazo - graphical frontend for SANE designed for mass-scanning
</pre>

<p>
We don't know the package name for the MySQLdb module. We use the <b>apt-cache</b>
command to figure it out.
</p>

<pre>
$ sudo apt-get install python-mysqldb
</pre>

<p>
Here we install the Python interface to the MySQL database. 
Both _mysql and MySQL modules.
</p>


<p>
Next, we are going to create a new database user and a new database. 
We use the mysql client.
</p>

<pre>
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.0.67-0ubuntu6 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)
</pre>

<p>
We connect to the database using the root account. We show all available
databases with the SHOW DATABASES statement. 
</p>

<pre>
mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.02 sec)
</pre>

<p>
We create a new <b>testdb</b> database. We will use this database throughout 
the tutorial.
</p>

<pre>
mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623';
Query OK, 0 rows affected (0.00 sec)

mysql> USE testdb;
Database changed

mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye
</pre>

<p>
We create a new database user. We grant all privileges to this user
for all tables of the testdb database. 
</p>


<h2>_mysql module</h2>

<p>
The <code>_mysql</code> module implements the MySQL C API directly. 
It is not compatible with the Python DB API interface. Generally, 
the programmers prefer the object oriented MySQLdb module. 
We will concern ourself with the latter module. Here we present only 
one small example with the _mysql module. 
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import _mysql
import sys


con = None

try:

    con = _mysql.connect('localhost', 'testuser', 
        'test623', 'testdb')
        
    con.query("SELECT VERSION()")
    result = con.use_result()
    
    print "MySQL version: %s" % \
        result.fetch_row()[0]
    
except _mysql.Error, e:
  
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit(1)

finally:
    
    if con:
        con.close()
</pre>

<p>
The example will get and print the version of the 
MySQL database. For this, we use the 
<code>SELECT VERSION()</code> SQL statement.
</p>


<h2>MySQLdb module</h2>

<p>
MySQLdb is a thin Python wrapper around _mysql. It is compatible with the
Python DB API, which makes the code more portable. Using this model is 
the preferred way of working with the MySQL. 
</p>


<h2>First example</h2>

<p>
In the first example, we will get the version of
the MySQL database. 
</p>


<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

con = None

try:

    con = mdb.connect('localhost', 'testuser', 
        'test623', 'testdb');

    cur = con.cursor()
    cur.execute("SELECT VERSION()")

    data = cur.fetchone()
    
    print "Database version : %s " % data
    
except mdb.Error, e:
  
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
    
finally:    
        
    if con:    
        con.close()
</pre>

<p>
In this script, we connect to the testdb database and execute the 
<code>SELECT VERSION()</code> statement. This will return 
the current version of the MySQL database. We print it to the console. 
</p>

<pre class="explanation">
import MySQLdb as mdb
</pre>

<p>
We import the <code>MySQLdb</code> module. 
</p>

<pre class="explanation">
con = None
</pre>

<p>
We initialize the con variable to None. In case we could not create 
a connection to the database (for example the disk is full), we would 
not have a connection variable defined. This would lead to 
an error in the finally clause.
</p>


<pre class="explanation">
con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb');
</pre>

<p>
We connect to the database. The <code>connect()</code> method has
four parameters. The first parameter is the host, where the MySQL database
is located. In our case it is a localhost, e.g. our computer. The 
second parameter is the database user name. It is followed by the
user's account password. The final parameter is the database name.
</p>

<pre class="explanation">
cur = con.cursor()
cur.execute("SELECT VERSION()")
</pre>

<p>
From the connection, we get the cursor object. The cursor is
used to traverse the records from the result set. We call 
the <code>execute()</code> method of the cursor and
execute the SQL statement. 
</p>

<pre class="explanation">
data = cur.fetchone()
</pre>

<p>
We fetch the data. Since we retrieve only one record, we
call the <code>fetchone()</code> method.
</p>

<pre class="explanation">
print "Database version : %s " % data
</pre>

<p>
We print the data that we have retrieved to the console. 
</p>

<pre class="explanation">
except mdb.Error, e:
  
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
</pre>

<p>
We check for errors. This is important, since working with databases is
error prone. 
</p>

<pre class="explanation">
finally:    
        
    if con:    
        con.close()
</pre>

<p>
In the final step, we release the resources. 
</p>


<pre class="code">
$ ./version.py
Database version : 5.5.9 
</pre>

<p>
The output might look like the above. 
</p>


<h2>Creating and populating a table</h2>

<p>
We create a table and populate it with some data.
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

with con:
    
    cur = con.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS \
        Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
    cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")

</pre>

<p>
We create a Writers table and add five authors to it. 
</p>

<pre class="explanation">
cur.execute("CREATE TABLE IF NOT EXISTS \
    Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
</pre>

<p>
This SQL statement creates a new database table called Writers. It has
two columns. Id and Name. 
</p>

<pre class="explanation">
cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
...
</pre>

<p>
We use the <code>INSERT</code> statement to insert authors to the table. 
Here we add two rows. 
</p>

<pre>
mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)
</pre>

<p>
After executing the script, we use the <code>mysql</code> client tool to 
select all data from the Writers table. 
</p>


<h2>Retrieving data</h2>

<p>
Now, that we have inserted some data into the database, 
we want to get it back.
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys


con = mdb.connect('localhost', 'testuser', 
        'test623', 'testdb');

with con: 

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers")

    rows = cur.fetchall()

    for row in rows:
        print row
</pre>

<p>
In this example, we retrieve all data from the Writers table. 
</p>

<pre class="explanation">
cur.execute("SELECT * FROM Writers")
</pre>

<p>
This SQL statement selects all data from the Writers
table. 
</p>

<pre class="explanation">
rows = cur.fetchall()
</pre>

<p>
The <code>fetchall()</code> method gets
all records. It returns a result set. Technically, it is
a tuple of tuples. Each of the inner tuples represent
a row in the table. 
</p>

<pre class="explanation">
for row in rows:
    print row
</pre>

<p>
We print the data to the console, row by row. 
</p>

<pre>
$ ./retrieve.py
(1L, 'Jack London')
(2L, 'Honore de Balzac')
(3L, 'Lion Feuchtwanger')
(4L, 'Emile Zola')
(5L, 'Truman Capote')
</pre>

<p>
This is the output of the example. 
</p>

<hr class="btm">

<p>
Returning all data at a time may not be feasible. We can fetch rows one
by one. 
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys


con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb');

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers")

    numrows = int(cur.rowcount)

    for i in range(numrows):
        row = cur.fetchone()
        print row[0], row[1]
</pre>

<p>
We again print the data from the Writers table to the
console. This time, we fetch the rows one by one.
</p>

<pre class="explanation">
numrows = int(cur.rowcount)
</pre>

<p>
Here we determine the number of rows returned by the
SQL statement. 
</p>

<pre class="explanation">
for i in range(numrows):
    row = cur.fetchone()
    print row[0], row[1]
</pre>

<p>
We fetch the rows one by one using the <code>fetchone()</code>
method. 
</p>

<pre>
$ ./retrieve2.py
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote
</pre>

<p>
Output of the example. 
</p>


<h2>The dictionary cursor</h2>

<p>
There are multiple cursor types in the MySQLdb module. 
The default cursor returns the data in a tuple of tuples. 
When we use a dictionary cursor, the data is sent in a form
of Python dictionaries. This way we can refer to the data
by their column names.
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb')

with con:

    cur = con.cursor(mdb.cursors.DictCursor)
    cur.execute("SELECT * FROM Writers")

    rows = cur.fetchall()

    for row in rows:
        print "%s %s" % (row["Id"], row["Name"])
</pre>

<p>
In this example, we print the contents of the Writers table
using the dictionary cursor.
</p>

<pre class="explanation">
cur = con.cursor(mdb.cursors.DictCursor)
</pre>

<p>
We use the <code>DictCursor</code> dictionary cursor. 
</p>

<pre class="explanation">
rows = cur.fetchall()
</pre>

<p>
We fetch all data. 
</p>

<pre class="explanation">
for row in rows:
        print "%s %s" % (row["Id"], row["Name"])
</pre>

<p>
We refer to the data by column names of the Writers
table.
</p>

<h2>Column headers</h2>

<p>
Next we will show, how to print column headers with the data
from the database table.
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys


con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb')

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers")

    rows = cur.fetchall()

    desc = cur.description

    print "%s %3s" % (desc[0][0], desc[1][0])

    for row in rows:    
        print "%2s %3s" % row
</pre>

<p>
Again, we print the contents of the Writers table to the
console. Now, we include the names of the columns too. The
column names are considered to be the 'meta data'. It is obtained
from the cursor object. 
</p>


<pre class="explanation">
desc = cur.description
</pre>

<p>
A Cursor Object's description attribute returns information about
each of the result columns of a query. 
</p>

<pre class="explanation">
print "%s %3s" % (desc[0][0], desc[1][0])
</pre>

<p>
Here we print and format the table column names. 
</p>


<pre class="explanation">
for row in rows:    
    print "%2s %3s" % row
</pre>

<p>
And here, we traverse and print the data.
</p>

<pre>
$ ./columnheaders.py
Id Name
 1 Jack London
 2 Honore de Balzac
 3 Lion Feuchtwanger
 4 Emile Zola
 5 Truman Capote
</pre>

<p>
Ouput of the script. 
</p>


<h2>Prepared statements</h2>

<p>
Now we will concern ourselves with prepared statements. When we write
prepared statements, we use placeholders instead of directly writing the
values into the statements. Prepared statements increase security and performance. 
The Python DB API specification suggests 5 different ways how
to build prepared statements. MySQLdb module supports one of them, the 
ANSI printf format codes. 
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys


con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb')
    
with con:    

    cur = con.cursor()
        
    cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Guy de Maupasant", "4"))        
    
    print "Number of rows updated: %d" % cur.rowcount
</pre>

<p>
We change the name of an author on the fourth row. 
</p>

<pre class="explanation">
cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
    ("Guy de Maupasant", "4"))   
</pre>

<p>
This is the way to do it. We use the two <code>%s</code> placeholders. Before
the SQL statement is executed, the values are bound to their
placeholders. 
</p>

<pre>
mysql> SELECT Name FROM Writers WHERE Id=4;
+------------------+
| Name             |
+------------------+
| Guy de Maupasant |
+------------------+
1 row in set (0.00 sec)
</pre>

<p>
The author on the fourth row was successfully changed. 
</p>


<h2>Writing images</h2>

<p>
Some people prefer to put their images into the database, some prefer to keep them 
on the file system for their applications. Technical difficulties arise when we 
work with millions of images. Images are binary data. MySQL database has a special 
data type to store binary data called <b>BLOB</b> (Binary Large Object). 
</p>

<pre class="code">
mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.06 sec)
</pre>

<p>
For this example, we create a new table called Images. 
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

try:
    fin = open("chrome.png")
    img = fin.read()
    fin.close()

except IOError, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

 
try:
    conn = mdb.connect(host='localhost',user='testuser',
       passwd='test623', db='testdb')
    cursor = conn.cursor()
    cursor.execute("INSERT INTO Images SET Data='%s'" % \
        mdb.escape_string(img))

    conn.commit()

    cursor.close()
    conn.close()

except mdb.Error, e:
  
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
</pre>

<p>
In the above script, we read a png image and insert it 
into the Images table. 
</p>

<pre class="explanation">
fin = open("chrome.png")
img = fin.read()
</pre>

<p>
We open and read an image. The <b>read()</b>
function returns the data as string. 
</p>

<pre class="explanation">
cursor.execute("INSERT INTO Images SET Data='%s'" % \
    mdb.escape_string(img))
</pre>

<p>
This string data is inserted into the table. Before doing so, it is processed
by the <b>escape_string()</b> method. 
It escapes a string for use as a query parameter. This is common practice 
to avoid malicious sql injection attacks. 
</p>

<h2>Reading images</h2>

<p>
In the previous example, we have inserted an image into the database table. 
Now we are going to read the image back from the table.
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb 
import sys

try:
    conn = mdb.connect(host='localhost',user='testuser', 
        passwd='test623', db='testdb')

    cursor = conn.cursor()

    cursor.execute("SELECT Data FROM Images LIMIT 1")

    fout = open('image.png','wb')
    fout.write(cursor.fetchone()[0])
    fout.close()

    cursor.close()
    conn.close()

except IOError, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
</pre>

<p>
We read one image from the Images table.
</p>


<pre class="explanation">
cursor.execute("SELECT Data FROM Images LIMIT 1")
</pre>

<p>
We select one record from the table. 
</p>

<pre class="explanation">
fout = open('image.png','wb')
</pre>

<p>
We open a writable binary file. 
</p>

<pre class="explanation">
fout.write(cursor.fetchone()[0])
</pre>

<p>
We fetch the data from the previous SQL
statement and write it to the file.
</p>

<p>
Now we should have an image called image.png in
our current directory. We can check if it is the same
image, that we have inserted into the table. 
</p>

<h2>Transaction support</h2>

<p>
A <code>transaction</code> is an atomic unit of database 
operations against the data in one or more databases. 
The effects of all the SQL statements in a transaction can be either 
all committed to the database or all rolled back.
</p>

<p>
For databases that support transactions, the Python interface silently starts a 
transaction when the cursor is created. The commit() method commits the 
updates made using that cursor, and the rollback() method discards them. 
Each method starts a new transaction.
</p>

<p>
The MySQL database has different types of storage engines. The most 
common are the MyISAM and the InnoDB engines. The MyISAM is the default one. 
There is a trade-off between data security and database speed. The MyISAM 
tables are faster to process and they do not support transactions.
The commit() and rollback() methods are not implemented. They do nothing. 
On the other hand, the InnoDB tables are more safe against the data loss. 
They support transactions. They are slower to process. 
</p>


<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

try:
    conn = mdb.connect('localhost', 'testuser', 
        'test623', 'testdb');

    cursor = conn.cursor()
    
    cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Leo Tolstoy", "1"))       
    cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Boris Pasternak", "2"))
    cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s", 
        ("Leonid Leonov", "3"))   

    conn.commit()

    cursor.close()
    conn.close()

except mdb.Error, e:
  
    conn.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
</pre>

<p>
In this script, we try to update three rows. The storage engine of
the table is MyISAM. 
</p>

<pre class="explanation">
cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
    ("Leo Tolstoy", "1"))       
cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
    ("Boris Pasternak", "2"))
</pre>

<p>
Here we want to change names of authors for rows 1 and 2. 
</p>

<pre class="explanation">
cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s", 
    ("Leonid Leonov", "3"))   
</pre>

<p>
There is an error in the SQL statement. There is no Writer table. 
</p>

<pre class="explanation">
conn.rollback()  
</pre>

<p>
We can call the <b>rollback()</b> method, but it does nothing.
</p>


<pre class="code">
$ ./isam.py
Error 1146: Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Leo Tolstoy       |
|  2 | Boris Pasternak   |
|  3 | Lion Feuchtwanger |
|  4 | Guy de Maupasant  |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)
</pre>

<p>
Running the script gives an error. But as we see, the first two rows
already were changed.
</p>

<p>
In the last example of this tutorial, we are going to recreate the 
Writers table. This time, the table will be of InnoDB type. 
InnoDB MySQL database tables support transactions. 
</p>

<pre class="code">
DROP TABLE Writers;

CREATE TABLE IF NOT EXISTS Writers(Id INT PRIMARY KEY AUTO_INCREMENT, 
    Name VARCHAR(25)) ENGINE=INNODB;

INSERT INTO Writers(Name) VALUES('Jack London');
INSERT INTO Writers(Name) VALUES('Honore de Balzac');
INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger');
INSERT INTO Writers(Name) VALUES('Emile Zola');
INSERT INTO Writers(Name) VALUES('Truman Capote');
</pre>

<p>
This is writers.sql file. It is used to recreate the Writers table.
</p>


<pre class="code">
mysql> source writers.sql
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)
</pre>

<p>
We can use the <b>source</b> commnad to load and execute the sql script. 
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

try:
    conn = mdb.connect('localhost', 'testuser', 
        'test623', 'testdb');

    cursor = conn.cursor()
    
    cursor.execute("DELETE FROM Writers WHERE Id = 5")  
    cursor.execute("DELETE FROM Writers WHERE Id = 4") 
    cursor.execute("DELETE FROM Writer WHERE Id = 3") 
    
    conn.commit()

except mdb.Error, e:
  
    conn.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

cursor.close()
conn.close()
</pre>

<p>
Now, we are going to execute the above script. We want to delete
three rows from the table. The third SQL statement has an error. 
</p>

<pre class="code">
$ ./innodb.py
Error 1146: Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)
</pre>

<p>
The error occured before we have committed the changes to the database. 
The <b>rollback()</b> method was called and no deletions took
place. 
</p>

<p>
This was MySQL Python tutorial. 
</p>

<div class="g-plusone"></div>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified August 10, 2010  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

